﻿Imports System.Data.SqlClient
Imports System.IO

Public Class DBObject

    Public Shared ReadOnly Property DBObjectConnectionString() As String
        Get
            Return "Data Source=62.149.153.37;database=MSSql60754;Integrated Security=False;uid=MSSql60754;pwd=ea61c20a;Connect Timeout=0;Encrypt=False;Packet Size=4096"

        End Get
    End Property

    Private _Connection As SqlConnection
    '<WebMethod(EnableSession:=True)> _
    Public Function Connection() As SqlConnection
        If IsNothing(_Connection) Then
            _Connection = New SqlConnection(DBObjectConnectionString)
        End If
        If _Connection.State <> ConnectionState.Open Then _Connection.Open()
        Return _Connection
    End Function

    


#Region "CLASSIC WS FUNCTION"


    Public Function FillDS(ByVal sql As String, ByVal params As Object(), ByVal cmdType As Integer) As DataSet
        Try
            Dim da As New SqlDataAdapter(sql, DBObjectConnectionString)
            da.SelectCommand.CommandType = cmdType
            da.SelectCommand.CommandTimeout = 600
            If Not IsNothing(params) Then
                For Each obj As Object In params
                    da.SelectCommand.Parameters.Add(New SqlParameter("", IIf(IsNothing(obj), DBNull.Value, obj)))
                Next
            End If
            Dim ds As New DataSet
            da.Fill(ds)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Function FillDSDBObject(ByVal sql As String, ByVal params As ArrayList, ByVal cmdType As Integer, ByVal cacheTableName As String) As DataSet
        Dim cacheStr As New System.Text.StringBuilder
        cacheStr.Append(sql)

        If Not IsNothing(params) Then
            For Each param As Object In params
                If IsNothing(param) Then param = "-"
                cacheStr.Append(param.ToString)
            Next
        End If



        Dim da As New SqlDataAdapter(sql, DBObjectConnectionString)
        da.SelectCommand.CommandType = cmdType
        da.SelectCommand.CommandTimeout = 600

        If Not IsNothing(params) Then
            For Each obj As Object In params
                da.SelectCommand.Parameters.Add(New SqlParameter("", IIf(IsNothing(obj), DBNull.Value, obj)))
            Next
        End If

        Dim ds As New DataSet
        da.Fill(ds)



        Return ds

    End Function
    ''' <summary>
    ''' Esegue la query e restituisce la prima colonna della prima riga nel gruppo di risultati restituito dalla query. Le colonne o le righe aggiuntive vengono ignorate.
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <param name="params"></param>
    ''' <param name="cmdType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteScalar(ByVal sql As String, ByVal params As Object(), ByVal cmdType As Integer) As Object
        Dim cmd As New SqlCommand(sql, Connection())

        Try
            cmd.CommandType = cmdType

            If Not IsNothing(params) Then
                For Each obj As Object In params
                    cmd.Parameters.Add(New SqlParameter("", IIf(IsNothing(obj), DBNull.Value, obj)))
                Next

            End If

            Dim scalar As Object = cmd.ExecuteScalar()

            If IsDBNull(scalar) Then scalar = Nothing

            Return scalar

        Catch ex As Exception
            Throw ex

        Finally
            _Connection.Close()

        End Try

    End Function
    ''' <summary>
    ''' Consente di eseguire un'istruzione Transact-SQL in base alla connessione e restituisce un numero di righe modificate. 
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <param name="params"></param>
    ''' <param name="cmdType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteNonQuery(ByVal sql As String, ByVal params As ArrayList, ByVal cmdType As Integer) As Integer
        Dim cmd As New SqlCommand(sql, Connection())
        Try
            cmd.CommandType = cmdType

            If Not IsNothing(params) Then
                For Each obj As Object In params
                    cmd.Parameters.Add(New SqlParameter("", IIf(IsNothing(obj), DBNull.Value, obj)))
                Next

            End If
            Return cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            _Connection.Close()

        End Try
    End Function

    Public Function ExecSp(ByVal spName As String, ByVal params As DataTable) As DataSet
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(spName, DBObjectConnectionString)
        da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure

        If Not IsNothing(params) AndAlso params.Rows.Count > 0 Then
            For Each dr As DataRow In params.Rows
                da.SelectCommand.Parameters.Add(New SqlParameter(CStr(dr(0)), dr(1)))

            Next

        End If

        da.Fill(ds)

        Return ds

    End Function

    Public Function FillSchema(ByVal sql As String, ByVal params As ArrayList, ByVal cmdType As Integer) As DataSet
        Dim da As New SqlDataAdapter(sql, DBObjectConnectionString)
        da.SelectCommand.CommandType = cmdType

        If Not IsNothing(params) Then
            For Each obj As Object In params
                da.SelectCommand.Parameters.Add(New SqlParameter("", IIf(IsNothing(obj), DBNull.Value, obj)))
            Next

        End If

        Dim ds As New DataSet
        da.FillSchema(ds, SchemaType.Mapped)

        Return ds

    End Function

#End Region

    Public Shared Sub WriteLog(text As String)
        Dim fp As StreamWriter

        Try
            fp = File.CreateText("\log\test.txt")
            fp.WriteLine(text)
            'lblStatus.Text = "File Succesfully created!"
            fp.Close()
        Catch err As Exception
            ' lblStatus.Text = "File Creation failed. Reason is as follows"
        Finally

        End Try
    End Sub

End Class
